package ils.domain.catalog.model;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

public class CategoryRepositoryImpl implements CategoryRepository
{
    private static final Logger logger = LoggerFactory.getLogger(CategoryRepositoryImpl.class);
    private SessionFactory sessionFactory;
    private static final String SELECT_RELATIVES = "select * from categories where id in (" +
            "select category1 as id from category_related where category2 = :id union select category2 as id from category_related where category1 = :id)";

    @Override
    public Category createCetegory(Category category)
    {
        logger.debug("entered category factory");
        Session session = sessionFactory.getCurrentSession();
        session.save(category);
        return category;
    }
    @Override
    public Category getCategory(int id)
    {
        Session session = sessionFactory.getCurrentSession();
        return (Category)session.get(Category.class, id);
    }
    public void setSessionFactory(SessionFactory sessionFactory)
    {
        this.sessionFactory = sessionFactory;
    }

    @Override
    public List<Category> getDescendants(Integer id) {
        Session session = sessionFactory.getCurrentSession();
        String fullName = "";
        if(id != null){
            Category category = getCategory(id);
            fullName = category.getFullName();
        }
        String sql = "select * from categories where fullName like :fullName order by level,fullName asc";
        Query query = session.createSQLQuery(sql).addEntity(Category.class);
        query.setString("fullName", fullName + "/%");
        return query.list();
    }

    @Override
    public List<Category> getRelated(int id) {
        Session session = sessionFactory.getCurrentSession();
        Query query = session.createSQLQuery(SELECT_RELATIVES).addEntity(Category.class);
        query.setInteger("id", id);
        return query.list();
    }

    @Override
    public Category getParent(int id) {
        Session session = sessionFactory.getCurrentSession();
        String sql = "select p.* from categories c, categories p where c.parentId = p.id and c.id = :id";
        Query query = session.createSQLQuery(sql).addEntity(Category.class).setInteger("id", id);
        return (Category)query.uniqueResult();
    }

    @Override
    public List<Category> getChildren(int id) {
        Session session = sessionFactory.getCurrentSession();
        String sql = "select ch.* from categories c, categories ch where c.id = ch.parentId and c.id = :id";
        Query query = session.createSQLQuery(sql).addEntity(Category.class).setInteger("id", id);
        return query.list();
    }

    @Override
    public Category findCategory(String name) {
        Session session = sessionFactory.getCurrentSession();
        String sql = "select * from categories where fullName = :name";
        Query query = session.createSQLQuery(sql).addEntity(Category.class).setString("name", name);
        return (Category)query.uniqueResult();
    }
}
